<?php
error_reporting(0);
date_default_timezone_set('Europe/London');

require_once 'Classes/PHPExcel/IOFactory.php';
require_once 'conn.ini';

$objReader = PHPExcel_IOFactory::createReader('Excel5');

$template_file = 'InstallmentTrackingReport.xls';
$objPHPExcel = $objReader->load("templates/".$template_file);

$data = null;
//echo $_GET['ids'];
$ids = str_replace("-", "," , $_GET['ids']);
//echo $ids;
$sql = "SELECT * FROM cc_paymentschedules WHERE id IN (" . $ids . ")";

$rs = mysql_query($sql);
if(mysql_num_rows($rs)) {
    
    $arrid = array();
    while ($row = mysql_fetch_array($rs, MYSQL_ASSOC)) {
        if (!in_array($row['accountresume_id'], $arrid)) {
            $arrid[] = $row['accountresume_id'];
        }
    }
    
    $a = array();
    foreach ($arrid as $obj) {
        $temp = array();
        $sql = "SELECT * FROM cc_paymentschedules WHERE accountresume_id = '" . $obj . "'";
        
        $rs = mysql_query($sql);
        if(mysql_num_rows($rs)) {
            $i=0;
            while ($row = mysql_fetch_array($rs, MYSQL_ASSOC)) {
                $paymentschedule = array(
                    'Paymentschedule' => $row);
                $paymentschedule['Paymentschedule']['installment'] = $i + 1;
                $paymentschedule['Paymentschedule']['outstanding'] = $paymentschedule['Paymentschedule']['broker_amount'] - $paymentschedule['Paymentschedule']['paid_amount'];

                if ($paymentschedule['Paymentschedule']['outstanding'] == 0) {
                    $paymentschedule['Paymentschedule']['alltoaih'] = 1;
                } else {
                    $paymentschedule['Paymentschedule']['alltoaih'] = 0;
                }

                $arrs = json_decode($paymentschedule['Paymentschedule']['markets'], true);
                $arrspaid = json_decode($paymentschedule['Paymentschedule']['placement_paid'], true);

                $paymentschedule['Paymentschedule']['arrmarket'] = $arrs;
                $paymentschedule['Paymentschedule']['arrmarketpaid'] = $arrspaid;

                $sub_sql = "SELECT * FROM cc_accountresumes WHERE refid = '" . $paymentschedule['Paymentschedule']['accountresume_id'] . "'";
                $sub_rs = mysql_query($sub_sql);
                $sub_row = mysql_fetch_array($sub_rs, MYSQL_ASSOC);
                $paymentschedule['Paymentschedule']['accid'] = $sub_row['id'];

                $temp[] = $paymentschedule;
                $i++;
            }
            $a[] = $temp;
        }
    }
    
//    echo '<pre>';
//    print_r($a);die;
}

$arr_col = array(0 => 'I', 1 => 'J', 2 => 'K', 3 => 'L', 4 => 'M', 5 => 'N', 6 => 'O', 7 => 'P', 8 => 'Q', 9 => 'R');
$arr_col_copy = array(0 => 'B', 1 => 'C', 2 => 'D', 3 => 'E', 4 => 'F', 5 => 'G', 6 => 'H', 7 => 'I', 8 => 'J', 9 => 'K', 10 => 'L', 11 => 'M', 12 => 'N', 13 => 'O', 14 => 'P', 15 => 'Q', 16 => 'R');
$max = 0;
$arr_max = null;
$arr_row = null;
$arr_to = null;
$arr_to_checked = null;
$i = 0;
foreach ($a as $key => $value) {
    $arr_row[$i] = count($value);
    $j = 0;
    foreach ($value as $v) {
        $data[$i][$j]['reference_id'] = $v['Paymentschedule']['refid_edit'];
        $data[$i][$j]['payment_id'] = $v['Paymentschedule']['refid'];
        $data[$i][$j]['installment_id'] = $v['Paymentschedule']['installment'];
        $data[$i][$j]['100_to_aih'] = ($v['Paymentschedule']['alltoaih'] == '1') ? '√' : '';
        $data[$i][$j]['amount'] = $v['Paymentschedule']['currency'].' '.number_format($v['Paymentschedule']['broker_amount'],2);
        $data[$i][$j]['outstanding'] = $v['Paymentschedule']['currency'].' '.number_format($v['Paymentschedule']['outstanding'],2);
        
        if($j == 0) {
            $max = (count($v['Paymentschedule']['arrmarket']) - 1 > $max) ? count($v['Paymentschedule']['arrmarket']) - 1 : $max;
            $arr_max[$i] = count($value);
            $k = 6;
            foreach ($v['Paymentschedule']['arrmarket'] AS $arrmarket) {
                $arr_to[$i][$j][$k] = 'To ' . $arrmarket[0];
                $k++;
            }
        }
        
        $k = 0;
        $arrpaid = $v['Paymentschedule']['arrmarketpaid'];
        foreach ($v['Paymentschedule']['arrmarket'] AS $arrmarket_key => $arrmarket) {
            $checked = '';
            if($arrpaid != null){
                if($arrpaid[$arrmarket_key]['paid'] == 'on'){
                    $checked = '√';
                }
            }
            $arr_to_checked[$i][$j][$k] = $checked;
            $k++;
        }
        
        $data[$i][$j]['to_aih'] = ($v['Paymentschedule']['paid_to_aih'] == 1) ? '√' : '';
        
        $j++;
    }
    
    $i++;
}

$repeat = $i;
$baseRowCopy = 10;
$baseRowCopy2 = 11;
$baseRowCopy3 = 12;
$baseRowRepeat = 14;
$baseRowRepeat2 = 15;
$baseRowRepeat3 = 16;

//echo '<pre>';
//print_r($data);die;

$baseRow = 12;
if($data != null) {
    // col process
    if($max > 0) {
        for($i_col = 0; $i_col < $max; $i_col++) {
            $objPHPExcel->getActiveSheet()->insertNewColumnBefore('I',1);
            $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
        }
    }
    
    // repeat
    $max_col = 8 + $max;
    for($j = 0; $j < $max_col; $j++) {
        if($j >= 6 && $j < $max_col -1) {
            $objPHPExcel->getActiveSheet()->setCellValue($arr_col_copy[$j] . $baseRowCopy, $arr_to[0][0][$j]);
        }
        $objPHPExcel->getActiveSheet()->getRowDimension($baseRowCopy)->setRowHeight(-1);
    }
    
    for($i = 1; $i < $repeat; $i++) {
        $objPHPExcel->getActiveSheet()->insertNewRowBefore($baseRowRepeat,1);
        $objPHPExcel->getActiveSheet()->insertNewRowBefore($baseRowRepeat,1);
        $objPHPExcel->getActiveSheet()->insertNewRowBefore($baseRowRepeat,1);
        $objPHPExcel->getActiveSheet()->insertNewRowBefore($baseRowRepeat,1);
        
        for($j = 0; $j < $max_col; $j++) {
            $objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle($arr_col_copy[$j] . $baseRowCopy), $arr_col_copy[$j] . $baseRowRepeat );
            $objPHPExcel->getActiveSheet()->setCellValue($arr_col_copy[$j] . $baseRowRepeat, $objPHPExcel->getActiveSheet()->getCell($arr_col_copy[$j] . $baseRowCopy)->getValue());
            if($j >= 6 && $j < $max_col -1) {
                $objPHPExcel->getActiveSheet()->setCellValue($arr_col_copy[$j] . $baseRowRepeat, $arr_to[$i][0][$j]);
            }
            $objPHPExcel->getActiveSheet()->getRowDimension($baseRowRepeat)->setRowHeight(-1);
            
            $objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle($arr_col_copy[$j] . $baseRowCopy2), $arr_col_copy[$j] . $baseRowRepeat2 );
            $objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle($arr_col_copy[$j] . $baseRowCopy3), $arr_col_copy[$j] . $baseRowRepeat3 );
        }
        
        $baseRowRepeat = $baseRowRepeat + 4;
        $baseRowRepeat2 = $baseRowRepeat2 + 4;
        $baseRowRepeat3 = $baseRowRepeat3 + 4;
    }
    
    // fill data
    $i = 0;
    foreach ($data as $rs => $dataRows) {
        $j = 0;
        if($i > 0) {
            $baseRow = $baseRow + 4 + $arr_row[$i - 1] - 2;
        }
        
        foreach ($dataRows as $r => $dataRow) {
            $row = $baseRow + $j;
            $objPHPExcel->getActiveSheet()->insertNewRowBefore($row,1);

            if($j == 0) {
                $objPHPExcel->getActiveSheet()->setCellValue('B'.$row, $dataRow['reference_id']);
            }
            $objPHPExcel->getActiveSheet()->setCellValue('C'.$row, $dataRow['payment_id']);
            $objPHPExcel->getActiveSheet()->setCellValue('D'.$row, $dataRow['installment_id']);
            $objPHPExcel->getActiveSheet()->setCellValue('E'.$row, $dataRow['100_to_aih']);
            $objPHPExcel->getActiveSheet()->setCellValue('F'.$row, $dataRow['amount']);
            $objPHPExcel->getActiveSheet()->setCellValue('G'.$row, $dataRow['outstanding']);
            
            $k = 6;
            foreach ($arr_to_checked[$i][$j] as $checked) {
                $objPHPExcel->getActiveSheet()->setCellValue($arr_col_copy[$k].$row, $checked);
                $k++;
            }
            $objPHPExcel->getActiveSheet()->setCellValue($arr_col_copy[$k].$row, $dataRow['to_aih']);
            
            $objPHPExcel->getActiveSheet()->getRowDimension($row)->setRowHeight(-1);
            $j++;
        }
        
        $objPHPExcel->getActiveSheet()->removeRow($baseRow-1,1);
        $objPHPExcel->getActiveSheet()->removeRow($baseRow+$j-1,1);
        $merge_from = $baseRow-1;
        $merge_to = $baseRow-1+$arr_row[$i]-1;
        $objPHPExcel->getActiveSheet()->mergeCells('B'.$merge_from.':'.'B'.$merge_to);
        
        $i++;
    }
    
}

mysql_close();
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="InstallmentTrackingReport.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
